USE [DSRSystem]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spGetIndicatorTemplates]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spGetIndicatorTemplates]
GO

CREATE PROCEDURE [dbo].[spGetIndicatorTemplates]
AS
BEGIN
	DECLARE @IndicatorTemplates TABLE
	(
		[IndicatorId] [uniqueidentifier] NOT NULL,
		[IndicatorName] [nvarchar](max) NOT NULL,
		[IndicatorNumber] [nvarchar](max) NOT NULL,
		[FormatedIndicatorNumber] [nvarchar](max) NOT NULL,
		[IndicatorSortKey] [int] NOT NULL,
		[IssueName] [nvarchar](max) NOT NULL,
		[IssueNumber] int NOT NULL
	)
	INSERT INTO @IndicatorTemplates

	SELECT	dbo.Indicators.IndicatorId, 
			dbo.Indicators.Name AS IndicatorName, 
			dbo.Indicators.Number AS IndicatorNumber,
			[dbo].[fFormatIndicatorNumber](dbo.Indicators.Number) AS FormatedIndicatorNumber,
			0 AS IndicatorSortKey, 
			dbo.IndicatorTopics.Name AS IssueName, 
			CONVERT(int, SUBSTRING(dbo.Indicators.Number, 3, 1)) AS IssueNumber
	FROM	dbo.Indicators
    INNER JOIN dbo.IndicatorTopics ON dbo.Indicators.TopicId = dbo.IndicatorTopics.TopicId 
    INNER JOIN dbo.IndicatorThemes ON dbo.IndicatorTopics.ThemeId = dbo.IndicatorThemes.ThemeId 
	WHERE dbo.Indicators.Template = 1 AND dbo.Indicators.IndicatorTypeId = 1

	UPDATE @IndicatorTemplates
	SET IndicatorSortKey = Indicators.IndicatorSortKey 
	FROM
	(
		SELECT IndicatorId AS Id, CONVERT(int, SUBSTRING(IndicatorNumber, 3, 1) * 100 + 
		RIGHT(CONVERT(VARCHAR, ROW_NUMBER() OVER(PARTITION BY IssueNumber ORDER BY FormatedIndicatorNumber)), 3)) AS IndicatorSortKey 
		FROM @IndicatorTemplates
	) Indicators
	WHERE Indicators.Id = IndicatorId

	SELECT	IndicatorId, 
			IndicatorName, 
			SUBSTRING(IndicatorNumber, 3, 10) AS IndicatorNumber, 
			IndicatorSortKey,
			IssueName, 
			IssueNumber 
	FROM @IndicatorTemplates 
	WHERE IssueNumber > 0
	ORDER BY IndicatorSortKey
	
END
GO


